﻿using EasyDater.Base;
using EasyDater.Built;
using EasyDater.Helper;
using Microsoft.Data.SqlClient;
using System.Collections;
using System.Data;

namespace EasyDater
{

    //来自 BuiltSQL和BuiltEntity
    public class EasyDater
    {

        BuiltSQL builtSQL = new BuiltSQL();
        BuiltEntity builtEntity = new BuiltEntity();
        #region  操作实体类

        /// <summary>
        /// 增加一行
        /// <param name="t">实体</param>
        /// <returns>执行结果</returns>
        /// </summary>
        public string Add<T>(T t) where T : class
        {
            return builtEntity.InsertTable<T>(t);
        }
        /// <summary>
        /// 删除一行
        /// <param name="t">实体</param>
        /// <returns>执行结果</returns>
        /// </summary>
        public string Del<T>(T t) where T : class
        {
            return builtEntity.DeleteTable<T>(t);
        }
        /// <summary>
        /// 更新一行
        /// <param name="t">实体</param>
        /// <returns>执行结果</returns>
        /// </summary>
        public string Update<T>(T t) where T : class
        {
            return builtEntity.UpdateTable<T>(t);
        }
        /// <summary>
        /// 更新一行的一个字段或几个字段
        /// <param name="T">实体类名</param>
        /// <param name="sdList">键值对，必须包括主键及主键的值（这是指定更新那一条记录的必须条件）</param>
        /// <returns>执行是否正确</returns>
        /// </summary>
        public bool Update<T>(SortedDictionary<string, string> sdList) where T : class
        {
            Type clsType = typeof(T);
            string tableName = clsType.Name;//类名就是表名
            return builtSQL.UpdateTable(tableName, sdList);
        }

        public T? GetOne<T>(T t) where T : class
        {
            MSTP? mSTP=builtEntity.GetOneMSTP<T>(t);
            return GetOne<T>(mSTP);

        }
            /// <summary>
            /// 获取一个实体类
            /// </summary>
            /// <param name="T">实体类</param>
            /// <param name="MSTP">数据 [key,value,type] TYPE只能是S（字符串）,D（数字）二选一</param>     
            /// <returns>t</returns>
            /// </summary>
            public T? GetOne<T>(MSTP mSTP) where T : class
        {
            if (mSTP is null) return default(T);
            Type clsType = typeof(T);
            string mainTable = clsType.Name;//类名就是表名
            DataTable? dt = GetOne(mainTable, mSTP);
            if (dt is null) return default(T);
            else if (dt.Rows.Count == 0) return default(T);

            T t = ModelTool.DataRowToModel<T>(dt.Rows[0]);

            return t;

        }


        /// <summary>
        /// 获取实体类列表
        /// </summary>
        /// <param name="T">实体类</param>
        /// <param name="sql">传入SQL语句</param>     
        /// <returns>t</returns>
        /// </summary>
        public List<T> GetList<T>(string sql) where T : class
        {

            List<T> list = new List<T>();
            DataTable? dt = SQLHelper.GetDataTable(sql);
            if (dt is null) return list;
            return ModelTool.DataTableToModels<T>(dt);

        }
     
        /// <summary>
        /// 获取实体类列表
        /// </summary>
        /// <param name="T">实体类</param>
        /// <param name="procName">存储过程</param>     
        /// <param name="coll">存储过程的参数</param>  
        /// <returns>t</returns>
        /// </summary>
        public List<T> GetList<T>(string procName, SqlParameter[] coll) where T : class
        {

            List<T> list = new List<T>();
            DataTable? dt = SQLHelper.ExecutePorcedureGetDataSet(procName, coll).Tables[0];
            if (dt is null) return list;
            return ModelTool.DataTableToModels<T>(dt);

        }
        //
        #endregion
        //########################################################################
        #region 操作SQL语句

        #region  insert
        //
        public bool InsertTable(string tableName, SortedDictionary<string, string> sdList)
        {
            return builtSQL.InsertTable(tableName, sdList);
        }
        public bool InsertTable(string tableName, SortedDictionary<string, string> sdList, string sql)
        {
            return builtSQL.InsertTable(tableName, sdList, sql);
        }
        public bool InsertTable(string tableName, SortedDictionary<string, string> sdList, ArrayList arrayList)
        {
            return builtSQL.InsertTable(tableName, sdList, arrayList);
        }

        #endregion
        #region  delete
        public bool DeleteTable(string tableName, string primaryKey, int primaryValue)
        {
            return builtSQL.DeleteTable(tableName, primaryKey, primaryValue);
        }
        public bool DeleteTable(string tableName, string primaryKey, string primaryValue)
        {
            return builtSQL.DeleteTable(tableName, primaryKey, primaryValue);
        }
        public bool DeleteTable(string tableName, string primaryKey, int primaryValue, string sql)
        {
            return builtSQL.DeleteTable(tableName, primaryKey, primaryValue, sql);
        }
        public bool DeleteTable(string tableName, string primaryKey, string primaryValue, string sql)
        {
            return builtSQL.DeleteTable(tableName, primaryKey, primaryValue, sql);
        }
        public bool DeleteTable(string tableName, string primaryKey, int primaryValue, ArrayList arrayList)
        {
            return builtSQL.DeleteTable(tableName, primaryKey, primaryValue, arrayList);
        }
        #endregion
        #region update
        public bool UpdateTable(string tableName, SortedDictionary<string, string> sdList)
        {
            return builtSQL.UpdateTable(tableName, sdList);
        }

        public bool UpdateTable(string tableName, SortedDictionary<string, string> sdList, ArrayList arrayList)
        {
            return builtSQL.UpdateTable(tableName, sdList, arrayList);
        }
        #endregion


        #region  search
        //根据主键，获取一条数据
        //大多用在显示数据和更改数据是使用
        public DataTable? GetOne(string tableName, MSTP primaryKey)
        {
            string strMainSQL = "Select * from " + tableName + " WHERE " + primaryKey.Items[0] + "=";
            if (primaryKey.Items[2] == "S")
                strMainSQL += "'" + primaryKey.Items[1].Trim() + "'";
            else
                strMainSQL += int.Parse(primaryKey.Items[1].Trim());

            return SQLHelper.GetDataTable(strMainSQL);
        }
        //获取一个数值,仅仅获取一个字段使用
        public string GetNumberExecSQL(string sqlstr)
        { return SQLHelper.ExecuteSqlGetNumber(sqlstr); }

        //根据输入条件查询整个表的数据
        public DataTable? GetListExecSQL(string strSQL)
        {
            return SQLHelper.GetDataTable(strSQL);
        }

        public DataSet GetDataSetExecSQL(string strSQL)
        {
            return SQLHelper.ExecuteSqlGetDataSet(strSQL);

        }
        //带事务执行SQL语句
        public bool GetBooolExecSQL(string strSQL)
        {
            return SQLHelper.ExecuteSqlWithTransaction(strSQL);

        }


        #endregion
        #region 执行存储过程
        /// <summary>
        /// 执行存储过程,返回是否执行成功
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="coll">参数数组SqlParameter[]</param>
        public  bool ExecutePorcedure(string procName, SqlParameter[] coll)
        {
            return SQLHelper.ExecutePorcedure( procName,  coll);

        }

        /// <summary>
        /// 执行指定数据库连接字符串的命令,返回结果集中的第一行第一列.
        /// </summary>
        /// <remarks>
        /// 示例: 
        /// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount",coll);
        /// </remarks>
        /// <param name="connectionString">一个有效的数据库连接字符串</param>
        /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="commandText">存储过程名称或T-SQL语句</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public  string? ExecutePorcedureGetNumber(string procName, SqlParameter[] coll)
        {
            return SQLHelper.ExecutePorcedureGetNumber(procName, coll);
        }
        /// <summary>
        /// 执行存储过程，并返回数据 DataSet
        /// </summary>
        /// <param name="procName"> 存储过程名称</param>
        /// <param name="coll">SqlParameter[]参数列表</param>
        /// <returns>DataSet ds</returns>
        public  DataSet ExecutePorcedureGetDataSet(string procName, SqlParameter[] coll)
        {
            return SQLHelper.ExecutePorcedureGetDataSet(procName, coll);
        }
        //12: 执行存储过程，并返回数据  DataTable 同时有输出参数
        public  DataTable ExecutePorcedureGetDataTable(string procName, SqlParameter[] coll, out int allRows)
        {
            return SQLHelper.ExecutePorcedureGetDataTable(procName, coll,out allRows);
        }

        /// <summary>
        /// 执行存储过程 返回当前插入记录的自动ID
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <returns>dt</returns>

        //执行存储过程，返回当前插入记录的自动ID
        public  object ExecutePorcedureGetID(string procName, SqlParameter[] coll)
        {
            return SQLHelper.ExecutePorcedureGetID(procName, coll);
        }
        #endregion

        #region  主表+明细表
        public string InsertMainAndDetail(string mainTable, SortedDictionary<string, string> mainList, string detailTable, List<SortedDictionary<string, string>> detailList)
        {
            return builtSQL.InsertMainAndDetail(mainTable, mainList, detailTable, detailList);
        }
        public string UpdateMainAndDetail(string mainTable, SortedDictionary<string, string> mainList, string[] primaryKey, string detailTable, List<SortedDictionary<string, string>> detailList)
        {
            return builtSQL.UpdateMainAndDetail(mainTable, mainList, primaryKey, detailTable, detailList);
        }
        #endregion
        #endregion
    }
}
